﻿CREATE DATABASE KCL
GO
USE KCL
CREATE TABLE [Company](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](100) NOT NULL,
	[address] [nvarchar](100) NULL,
	[phone] [nvarchar](20) NULL,
	[mobile] [nvarchar](20) NULL,
	[fax] [nvarchar](20) NULL,
	[accountNumber] [nvarchar](50) NULL,
	[bank] [nvarchar](100) NULL,
	[taxNumber] [nvarchar](50) NULL,
	[director] [nvarchar](100) NULL,
	[logo] [nvarchar](200) NULL,
	CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [Company]([name]) VALUES(N'Công ty TNHH Hoàng Gia')
CREATE TABLE [User](
	[id] INT IDENTITY(1,1) PRIMARY KEY,
	[username] nvarchar(100) NOT NULL,
	[password] nvarchar(100) NOT NULL,
	[fullname] nvarchar(100) NOT NULL,
	[email] nvarchar(100) NULL,
	[code] nvarchar(10) NOT NULL,
	[admin] BIT NOT NULL DEFAULT 0,
	[read] BIT NOT NULL DEFAULT 1,
	[create] BIT NOT NULL DEFAULT 0,
	[edit] BIT NOT NULL DEFAULT 0,
	[isActive] BIT NOT NULL DEFAULT 1
)
INSERT INTO [User] VALUES('admin', 'DNC', 'admin DNC', 'dnc@gamil.com', 'dnc', 1, 1, 1, 1, 1)
CREATE TABLE [Customer](
	[id] INT IDENTITY(1,1) PRIMARY KEY,
	[name] nvarchar(100) NOT NULL,
	[address] nvarchar(100) NOT NULL,
	[contactAddress] nvarchar(100) NULL,
	[contactPerson] nvarchar(100) NULL,
	[phone] nvarchar(30) NULL,
	[mobile] nvarchar(30) NULL,
	[fax] nvarchar(30) NULL,
	[accountNumber] nvarchar(50) NULL,
	[bank] nvarchar(100) NULL,
	[taxNumber] nvarchar(50) NULL,
	[director] nvarchar(100) NULL,
	[code] nvarchar(10) NULL,
	[isActive] BIT NOT NULL DEFAULT 1
)
CREATE TABLE [Product](
	[id] INT IDENTITY(1,1) PRIMARY KEY,
	[name] nvarchar(50) NOT NULL,
	[code] nvarchar(20) NOT NULL,
	[isActive] BIT NOT NULL DEFAULT 1,
	CONSTRAINT uni_Product UNIQUE (Code)
)
CREATE TABLE [Color](
	[id] INT IDENTITY(1,1) PRIMARY KEY,
	[name] nvarchar(20) NOT NULL,
	[isActive] BIT NOT NULL DEFAULT 1,
	CONSTRAINT uni_Color UNIQUE (name)
)
INSERT INTO [Color]([name]) VALUES(N'Trắng')
INSERT INTO [Color]([name]) VALUES(N'Đen')
INSERT INTO [Color]([name]) VALUES(N'Đỏ')
INSERT INTO [Color]([name]) VALUES(N'Xanh nước biển')
INSERT INTO [Color]([name]) VALUES(N'Xanh lá cây')
CREATE TABLE [Size](
	[id] INT IDENTITY(1,1) PRIMARY KEY,
	[length] nvarchar(10) NOT NULL,
	[width] nvarchar(10) NOT NULL,
	[isActive] BIT NOT NULL DEFAULT 1,
	CONSTRAINT uni_Size UNIQUE ([length], [width])
)
CREATE TABLE [Thickness] (
	[id] INT IDENTITY(1,1) PRIMARY KEY,
	[thick] nvarchar(10) NOT NULL,
	[isActive] BIT NOT NULL DEFAULT 1,
	CONSTRAINT uni_Thickness UNIQUE ([thick])
)
CREATE TABLE [MadeIn](
	[id] INT IDENTITY(1,1) PRIMARY KEY,
	[name] nvarchar(30) NOT NULL,
	[isActive] BIT NOT NULL DEFAULT 1,
	CONSTRAINT uni_MadeIn UNIQUE (name)
)
INSERT INTO [MadeIn]([name]) VALUES(N'Việt Nam')
INSERT INTO [MadeIn]([name]) VALUES(N'Mỹ')
INSERT INTO [MadeIn]([name]) VALUES(N'Trung Quốc')
INSERT INTO [MadeIn]([name]) VALUES(N'Nhật Bản')
CREATE TABLE [Material](
	[code] nvarchar(100) PRIMARY KEY,
	[name] nvarchar(200),
	[productId] INT FOREIGN KEY REFERENCES [product]([id]) ON DELETE NO ACTION,
	[colorId] INT FOREIGN KEY REFERENCES [Color]([id]) ON DELETE NO ACTION,
	[sizeId] INT FOREIGN KEY REFERENCES [Size]([id]) ON DELETE NO ACTION,
	[thicknessId] INT FOREIGN KEY REFERENCES [Thickness]([id]) ON DELETE NO ACTION,
	[madeInId] INT FOREIGN KEY REFERENCES [MadeIn]([id]) ON DELETE NO ACTION,
	[quantity] INT NOT NULL DEFAULT 0,
	[isActive] BIT NOT NULL DEFAULT 1
)
CREATE TABLE [MaterialImport]
(
	[id]			INT		PRIMARY KEY,
	[materialCode]	NVARCHAR(100) FOREIGN KEY REFERENCES [Material]([code]),
	[quantity]		INT,
	[date]			DATETIME,
	[importer]		NVARCHAR(100),
	[note]			NVARCHAR(MAX)
)
CREATE TABLE [MaterialExport]
(
	[id]			INT		PRIMARY KEY,
	[materialCode]	NVARCHAR(100) FOREIGN KEY REFERENCES [Material]([code]),
	[quantity]		INT,
	[receivePlace]	NVARCHAR(100),
	[receiver]		NVARCHAR(100),
	[exporter]		NVARCHAR(100),
	[date]			DATETIME,
	[note]			NVARCHAR(MAX)
)
CREATE TABLE [Phase](
	[id] INT IDENTITY(1,1) PRIMARY KEY,
	[name] nvarchar(100) NOT NULL,
	[price] INT NOT NULL DEFAULT 0
)
INSERT INTO [Phase] VALUES(N'Cắt', 100)
INSERT INTO [Phase] VALUES(N'Mài song cạnh', 200)
INSERT INTO [Phase] VALUES(N'Mài cạnh đứng + vát cạnh', 300)
INSERT INTO [Phase] VALUES(N'Mài dị dạng', 400)
INSERT INTO [Phase] VALUES(N'Khoan lỗ', 500)
INSERT INTO [Phase] VALUES(N'Rửa', 600)
INSERT INTO [Phase] VALUES(N'Cường lực', 700)
INSERT INTO [Phase] VALUES(N'Ép 2 lớp', 800)
CREATE TABLE [PayMethod](
	[id] INT IDENTITY(1,1) PRIMARY KEY,
	[name] [nvarchar](100) NOT NULL,
	[type] INT NOT NULL DEFAULT 1,
	[amount] INT NULL
)
INSERT INTO [PayMethod] VALUES(N'Khi giao hóa don', 1, NULL)
INSERT INTO [PayMethod] VALUES(N'Sau ? ngày', 0, 7)
INSERT INTO [PayMethod] VALUES(N'Thu trước khi giao ?%', 0, 50)
CREATE TABLE [Package](
	[id] INT IDENTITY(1,1) PRIMARY KEY,
	[name] [nvarchar](100) NOT NULL
)
INSERT INTO [Package] VALUES(N'Kiệnn sắt')
INSERT INTO [Package] VALUES(N'Kiện gỗ')
INSERT INTO [Package] VALUES(N'Giao lẻ')
CREATE TABLE [Order](
	[id] INT IDENTITY(-2147483648,1) PRIMARY KEY,
	[customerId] INT FOREIGN KEY REFERENCES [Customer]([id]) ON DELETE NO ACTION,
	[strTotalPrice] nvarchar(100) NULL,
	[totalPrice] FLOAT NOT NULL DEFAULT 0,
	[discount] INT NULL,
	[prePay] INT NULL,
	[remain] INT NULL,
	[deliveryDate] DATE NULL,
	[createdDate] DATETIME NOT NULL DEFAULT GETDATE(),
	[createdBy] INT FOREIGN KEY REFERENCES [User]([id]) ON DELETE NO ACTION,
	[code] nvarchar(100) NOT NULL DEFAULT '',
	[payMethodId] INT FOREIGN KEY REFERENCES [PayMethod]([id]) ON DELETE NO ACTION,
	[packageId] INT FOREIGN KEY REFERENCES [Package]([id]) ON DELETE NO ACTION,
	[isFinished] BIT NOT NULL DEFAULT 0
)
CREATE TABLE [OrderMaterial](
	[id] INT IDENTITY(-2147483648,1) PRIMARY KEY,
	[orderId] INT FOREIGN KEY REFERENCES [Order]([id]) ON DELETE NO ACTION,
	[materialId] nvarchar(100) FOREIGN KEY REFERENCES [Material]([code]) ON DELETE NO ACTION,
	[description] nvarchar(100) NULL,
	[orderQuantity] INT NOT NULL DEFAULT 0,
	[finishedQuantity] INT NOT NULL DEFAULT 0,
	[finishedDate] DATE NULL
)
CREATE TABLE [OrderMaterialPhase](
	[orderMaterialId] INT FOREIGN KEY REFERENCES [OrderMaterial]([id]) ON DELETE NO ACTION,
	[phaseId] INT FOREIGN KEY REFERENCES [Phase]([id]) ON DELETE NO ACTION,
	[weight] FLOAT NOT NULL DEFAULT 1,
	[quantity] TINYINT NOT NULL DEFAULT 1,
	PRIMARY KEY ([orderMaterialId], [phaseId])
)
CREATE TABLE [OrderDebt](
	[orderCode] nvarchar(100) NOT NULL,
	[payTurn] INT NOT NULL DEFAULT 1,
	[payAmount] FLOAT NOT NULL DEFAULT 0,
	[payDate] DATETIME,
	PRIMARY KEY ([orderCode], [payTurn])
)
SELECT 'var ' + name + ' = context.Request.Form["' + name + '"] != null ? context.Request.Form["' + name + '"].Trim().Replace("''", "''''") : "";' FROM sys.columns WHERE object_id=(SELECT object_id FROM sys.tables WHERE name='Order')
SELECT * FROM [OrderDebt]
SELECT * FROM [OrderMaterial]
SELECT * FROM [OrderMaterialPhase]